{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "1f4f2bd5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "你好，世界\n",
      "你叫什么名字？\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "def trans(sen):\n",
    "    # -*- coding: utf-8 -*-\n",
    "    # This code shows an example of text translation from English to Simplified-Chinese.\n",
    "    # This code runs on Python 2.7.x and Python 3.x.\n",
    "    # You may install `requests` to run this code: pip install requests\n",
    "    # Please refer to `https://api.fanyi.baidu.com/doc/21` for complete api document\n",
    "  \n",
    "    import requests\n",
    "    import random\n",
    "    import json\n",
    "    from hashlib import md5\n",
    "\n",
    "    # Set your own appid/appkey.\n",
    "    appid = '20240322002001574'\n",
    "    appkey = '2JsL8kVt6n6v1GAcw7q1'############\n",
    "\n",
    "    # For list of language codes, please refer to `https://api.fanyi.baidu.com/doc/21`\n",
    "    from_lang = 'en'\n",
    "    to_lang =  'zh'\n",
    "\n",
    "    endpoint = 'http://api.fanyi.baidu.com'\n",
    "    path = '/api/trans/vip/translate'\n",
    "    url = endpoint + path\n",
    "\n",
    "    query = sen\n",
    "\n",
    "    # Generate salt and sign\n",
    "    def make_md5(s, encoding='utf-8'):\n",
    "        return md5(s.encode(encoding)).hexdigest()\n",
    "\n",
    "    salt = random.randint(32768, 65536)\n",
    "    sign = make_md5(appid + query + str(salt) + appkey)\n",
    "\n",
    "    # Build request\n",
    "    headers = {'Content-Type': 'application/x-www-form-urlencoded'}\n",
    "    payload = {'appid': appid, 'q': query, 'from': from_lang, 'to': to_lang, 'salt': salt, 'sign': sign}\n",
    "\n",
    "    # Send request\n",
    "    r = requests.post(url, params=payload, headers=headers)\n",
    "    result = r.json()\n",
    "\n",
    "    # Show response\n",
    "    # print(json.dumps(result, indent=4, ensure_ascii=False))\n",
    "    return json.dumps(result, indent=4, ensure_ascii=False)\n",
    "\n",
    "\n",
    "\n",
    "zhongwen = []\n",
    "import time\n",
    "#等待一秒\n",
    "\n",
    "for i in ['hello, world', 'what is your name']:\n",
    "    sen = i\n",
    "    jiexijson = json.loads(trans(sen))  # 翻译后的一个json文件\n",
    "    # print(jiexijson)\n",
    "    reslut = jiexijson['trans_result'][0]['dst']  # 从上面的json中提取翻译的结果\n",
    "    zhongwen.append(reslut)\n",
    "    time.sleep(1)\n",
    "    print(reslut)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "fd84a442",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 检查NaN值：pd.notna() 不是空那么返回true"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "017cb45e",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('data_dictionary.txt', 'r', encoding='utf-8') as infile, open('output.txt', 'w', encoding='utf-8') as outfile:\n",
    "    # 遍历输入文件的每一行\n",
    "    for line in infile:\n",
    "        # 如果行中不包含冒号(:)，则在行末加一个冒号\n",
    "        line = line.replace(': ', ':')\n",
    "        line = line.replace('\\t', ':')\n",
    "        if ':' not in line:\n",
    "            line = line.rstrip('\\n') + ':\\n'\n",
    "        # 将处理后的行写入输出文件\n",
    "        outfile.write(line)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "c8b6d32b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>price_doc</td>\n",
       "      <td>sale price (this is the target variable)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>id</td>\n",
       "      <td>transaction id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>timestamp</td>\n",
       "      <td>date of transaction</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>full_sq</td>\n",
       "      <td>total area in square meters, including loggias...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>life_sq</td>\n",
       "      <td>living area in square meters, excluding loggia...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>floor</td>\n",
       "      <td>for apartments, floor of the building</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>max_floor</td>\n",
       "      <td>number of floors in the building</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>material</td>\n",
       "      <td>wall material</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>build_year</td>\n",
       "      <td>year built</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>num_room</td>\n",
       "      <td>number of living rooms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>kitch_sq</td>\n",
       "      <td>kitchen area</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>state</td>\n",
       "      <td>apartment condition</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>product_type</td>\n",
       "      <td>owner-occupier purchase or investment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>sub_area</td>\n",
       "      <td>name of the district</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>full_all</td>\n",
       "      <td>subarea population</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>male_f, female_f</td>\n",
       "      <td>subarea population by gender</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>young_*</td>\n",
       "      <td>population younger than working age</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>work_*</td>\n",
       "      <td>working-age population</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>ekder_*</td>\n",
       "      <td>retirement-age population</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>n_m_{all|male|female}</td>\n",
       "      <td>population between n and m years old</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        0                                                  1\n",
       "2               price_doc           sale price (this is the target variable)\n",
       "3                      id                                     transaction id\n",
       "4               timestamp                                date of transaction\n",
       "5                 full_sq  total area in square meters, including loggias...\n",
       "6                 life_sq  living area in square meters, excluding loggia...\n",
       "7                   floor              for apartments, floor of the building\n",
       "8               max_floor                   number of floors in the building\n",
       "9                material                                      wall material\n",
       "10             build_year                                         year built\n",
       "11               num_room                             number of living rooms\n",
       "12               kitch_sq                                       kitchen area\n",
       "13                  state                                apartment condition\n",
       "14           product_type              owner-occupier purchase or investment\n",
       "15               sub_area                               name of the district\n",
       "19               full_all                                 subarea population\n",
       "20       male_f, female_f                       subarea population by gender\n",
       "21                young_*                population younger than working age\n",
       "22                 work_*                             working-age population\n",
       "23                ekder_*                          retirement-age population\n",
       "24  n_m_{all|male|female}               population between n and m years old"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取以冒号分隔的txt文件\n",
    "df = pd.read_csv('output.txt', delimiter=':', engine='python', header=None)\n",
    "df = df.dropna(subset = [0,1], how = 'any')\n",
    "# 显示前几行数据\n",
    "df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "f3c11564",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "销售价格（这是目标变量）\n",
      "事务id\n",
      "交易日期\n",
      "总面积（平方米），包括凉亭、阳台和其他非住宅区\n",
      "居住面积（平方米），不包括凉亭、阳台和其他非住宅区\n",
      "对于公寓，建筑的楼层\n",
      "建筑物的层数\n",
      "墙体材料\n",
      "建造年份\n",
      "客厅数量\n",
      "厨房区域\n",
      "公寓条件\n",
      "自住业主购买或投资\n",
      "地区名称\n",
      "分区人口\n",
      "按性别划分的分区人口\n",
      "低于工作年龄的人口\n",
      "劳动年龄人口\n",
      "退休年龄人群\n",
      "n至m岁的人口\n",
      "分区内按建筑类型或年份划分的建筑物\n",
      "该地产500米范围内的x数量\n",
      "x在该地产500米范围内的份额\n",
      "平方米\n",
      "酒店d米范围内平均账单低于p RUB的咖啡馆数量\n",
      "购物中心\n",
      "工业区\n",
      "绿色区域\n",
      "地铁\n",
      "乘车距离\n",
      "莫斯科环形汽车路\n",
      "第三传输环\n",
      "花园戒指\n",
      "林荫大道环\n",
      "市中心\n",
      "火车站\n",
      "肮脏的行业\n",
      "发电厂\n",
      "事务时间戳\n",
      "乌拉尔原油（美元/桶）\n",
      "国内生产总值\n",
      "实际GDP增长\n",
      "通货膨胀-消费价格指数增长\n",
      "通货膨胀-生产者价格指数增长\n",
      "通货膨胀-GDP平减指数\n",
      "贸易顺差\n",
      "贸易余额（占上年的百分比）\n",
      "卢布/美元汇率\n",
      "卢布/欧元汇率\n",
      "伦敦布伦特原油（美元/桶）\n",
      "资本净进出口\n",
      "按当前价格计算的GDP\n",
      "国内生产总值增长（按实际价值计算）\n",
      "俄罗斯订单供应（针对开发商）\n",
      "莫斯科订单供应（针对开发商）\n",
      "指数RTS/回报\n",
      "MICEX索引/返回\n",
      "政府债券MICEX指数（MICEX RGBI TR）/收益率\n",
      "MICEX指数公司债券（MICEX CBI TR）/收益率\n",
      "家庭存款量\n",
      "人口存款量增长\n",
      "存款平均利率\n",
      "抵押贷款量\n",
      "抵押贷款增长\n",
      "抵押贷款加权平均利率\n",
      "公寓所在地俄罗斯联邦主题的GRP\n",
      "公寓所在地俄罗斯联邦主题的地区生产总值增长\n",
      "人均收入\n",
      "人口实际可支配收入增长\n",
      "平均月薪\n",
      "名义工资增长\n",
      "用于购买力区域间比较的一篮子固定消费品和服务的成本\n",
      "零售贸易营业额\n",
      "人均零售贸易额\n",
      "零售额（以可比价格计，与上年同期的百分比）\n",
      "劳动力规模\n",
      "失业率\n",
      "就业率\n",
      "人均固定资本投资\n",
      "固定资产绝对投资额\n",
      "盈利企业份额\n",
      "未盈利企业的份额\n",
      "自有收入在合并预算总收入中的份额\n",
      "每人逾期工资\n",
      "公司人均财务业绩\n",
      "每千人结婚人数\n",
      "离婚率/增长率\n",
      "完成的建筑工程量（百万卢布）\n",
      "固定资产实物投资额指数（与上年同月可比价格，%）\n",
      "人口自然增长/减少率（1000人）\n",
      "人口迁移增加（减少）\n",
      "总人口增长\n",
      "分娩\n",
      "死亡率\n",
      "住房公积金（平方米）\n",
      "住宿（平方米/人）\n",
      "管道可用性（人）\n",
      "浴室可用性（人）\n",
      "渠道化可用性\n",
      "天然气（干线、液化）供应\n",
      "热水可用性\n",
      "地板电加热\n",
      "供暖可用性\n",
      "旧房和危房比例，%\n",
      "平均预期寿命\n",
      "婴儿死亡率（每1000名一岁以下儿童）\n",
      "围产期死亡率（每1000名活产婴儿）\n",
      "总人口的总体发病率\n",
      "商务舱4间公寓的租金\n",
      "商务舱三房公寓的租金\n",
      "商务舱2间公寓的租金\n",
      "商务舱一房公寓的租金\n",
      "经济舱三房公寓的租金\n",
      "经济舱2间公寓的租金\n",
      "经济舱一房公寓的租金\n",
      "学前教育机构的教师人数（每100名教师中的儿童人数）；\n",
      "等待确定进入可容纳100人的学前教育机构的儿童人数\n",
      "高中教师负担（休斯学校100名教师的学生人数）\n",
      "一班制高中学生占高中学生总数的比例\n",
      "国家（市）教育机构的份额，对应现代教育要求的高中总数；\n",
      "在国家（市）教育机构中，失修和需要大修的建筑物占总数的比例。\n",
      "该地区医生的配备（相对数量）\n",
      "提供护理人员\n",
      "医生的工作量（每位医生的就诊次数）\n",
      "门诊容量\n",
      "每10万人可获得的医院床位\n",
      "一年内医院床位的平均占用率\n",
      "零售空间\n",
      "为人口提供现代业态的零售空间，平方米\n",
      "人均零售贸易额\n",
      "餐饮业人均营业额\n",
      "每1000人中影院观众人数\n",
      "俄罗斯文化部礼堂每100000人口的座位总数\n",
      "每1000人参观博物馆的次数\n",
      "体育设施容量\n",
      "经常参加体育运动的人口比例\n",
      "学生和经常参加体育运动的学生占总数的比例\n",
      "城市住宅公寓建设\n",
      "城市住宅公寓基金\n",
      "面积mun。面积，平方米。\n",
      "市镇人口数量。地区\n",
      "绿化面积占总面积的比例\n",
      "工业区面积占总面积的比例\n",
      "学龄前人口数量\n",
      "学前教育机构的席位数量\n",
      "学前教育机构数量\n",
      "学龄儿童人口\n",
      "区域内的高中座位数量\n",
      "高中院校数量\n",
      "莫斯科前20名最佳学校的高中数量\n",
      "该地区的医院床位数量\n",
      "地区医疗保健中心数量\n",
      "联邦排名前十的高等教育机构数量\n",
      "高等教育机构数量\n",
      "附加教育组织的数量\n",
      "文化遗产的关键对象的存在（RF组成实体层面的重要对象，城市）\n",
      "文化遗产数量\n",
      "区内商场和购物中心的数量\n",
      "区内商场和购物中心的数量\n",
      "区域内有火力发电站\n",
      "存在焚烧炉\n",
      "肮脏行业的存在\n",
      "存在放射性废物处置\n",
      "该地区铁路终点站的存在\n",
      "大型杂货/批发市场的存在\n",
      "现有核反应堆的存在\n",
      "拘留中心、监狱的存在\n",
      "市镇总人口\n",
      "男性人口\n",
      "女性人口\n",
      "低于工作年龄的人口\n",
      "低于工作年龄的男性人口\n",
      "年龄小于工作年龄的女性\n",
      "劳动年龄人口\n",
      "男性工作年龄人口\n",
      "女性工作年龄人口\n",
      "超过工作年龄的人口\n",
      "年龄大于工作年龄的男性人口\n",
      "年龄大于工作年龄的女性人口\n",
      "0-6岁人口\n",
      "0-7岁男性人群\n",
      "0-8岁的女性人口\n",
      "7-14岁人口\n",
      "7-14岁男性\n",
      "7-14岁的女性人口\n",
      "0-17岁人口\n",
      "0-17岁男性\n",
      "0-17岁的女性人口\n",
      "16-19岁人口\n",
      "16-19岁的男性人口\n",
      "16-19岁的女性人口\n",
      "0-13岁人口\n",
      "0-13岁男性\n",
      "0-13岁的女性人口\n",
      "区域内有材料信息的建筑数量\n",
      "街区建筑份额\n",
      "木制建筑的份额\n",
      "框架建筑份额\n",
      "砖砌建筑份额\n",
      "整体式建筑的份额\n",
      "面板建筑份额\n",
      "泡沫建筑份额\n",
      "矿渣建筑份额\n",
      "混合建筑份额\n",
      "地区中具有建造年份信息的建筑数量\n",
      "1920年前建筑份额\n",
      "1921-1945年建筑份额\n",
      "1946-1970年建筑物份额\n",
      "1971-1995年建筑物份额\n",
      "1995年后建筑份额\n",
      "7-14岁男性\n",
      "7-14岁的女性人口\n",
      "0-17岁人口\n",
      "0-17岁男性\n",
      "0-17岁的女性人口\n",
      "16-19岁人口\n",
      "16-19岁的男性人口\n",
      "16-19岁的女性人口\n",
      "0-13岁人口\n",
      "0-13岁男性\n",
      "0-13岁的女性人口\n",
      "乘坐地铁的时间，分钟。\n",
      "开车到地铁的距离，km\n",
      "步行去地铁的时间\n",
      "到地铁的距离，km\n",
      "到幼儿园的距离\n",
      "到高中的距离\n",
      "到公园的距离\n",
      "到绿化区的距离\n",
      "与工业区的距离\n",
      "到水处理的距离\n",
      "到墓地的距离\n",
      "到焚烧的距离\n",
      "到火车站的距离（步行）\n",
      "到火车站的时间（步行）\n",
      "最近的火车站id（步行）\n",
      "到火车站的距离（avto）\n",
      "到火车站的时间（avto）\n",
      "最近的火车站id（avto）\n",
      "到公共交通站的距离（步行）\n",
      "到公共交通站的时间（步行）\n",
      "到水库/河流的距离\n",
      "通往河流的第一条线路（150米）\n",
      "距离MKAD（莫斯科环形汽车路）\n",
      "到TTC（第三传输环）的距离\n",
      "到花园环的距离\n",
      "到林荫大道环的距离\n",
      "距离市中心（克里姆林宫）\n",
      "到最近主要道路的距离\n",
      "最近的大马路id\n",
      "通往道路的第一条线路（高速公路100米，MKAD 250米）\n",
      "到下一条远处主要道路的距离\n",
      "第二个最近的大马路id\n",
      "距离铁路/莫斯科中环/地下开放区域\n",
      "通往铁路的第一条线路（100米）\n",
      "到火车站的距离\n",
      "最近的铁路终点站id\n",
      "到公交总站的距离（avto）\n",
      "最近的总线终端id\n",
      "与肮脏行业的距离\n",
      "与核反应堆的距离\n",
      "放射性废物掩埋距离\n",
      "到输电线路的距离\n",
      "与火力发电厂的距离\n",
      "到发电站的距离\n",
      "到杂货/批发市场的距离\n",
      "与市场和百货公司的距离\n",
      "健身距离\n",
      "到游泳池的距离\n",
      "到冰宫的距离\n",
      "到体育场的距离\n",
      "到篮球场的距离\n",
      "到临终关怀/停尸房的距离\n",
      "到拘留设施的距离\n",
      "与公共医疗保健的距离\n",
      "与大学的距离\n",
      "与工作场所的距离\n",
      "到购物中心的距离\n",
      "到商业中心/办公室的距离\n",
      "远程附加教育\n",
      "与学前教育组织的距离\n",
      "到大教堂的距离\n",
      "与基督教教会和犹太会堂的距离\n",
      "与清真寺的距离\n",
      "到剧院的距离\n",
      "到博物馆的距离\n",
      "到展览的距离\n",
      "餐饮距离\n",
      "房屋所在的生态区\n",
      "500米区域中绿色区域的份额\n",
      "工业区在500米区域中的份额\n",
      "500米区域内的办公空间数量\n",
      "500米区域的办公面积\n",
      "500米区域内的购物中心数量\n",
      "500米区域内的购物中心广场\n",
      "500米区域内的咖啡馆或餐馆数量\n",
      "咖啡馆和餐厅500米区域的最低平均账单\n",
      "咖啡馆和餐厅500米区域的最高平均账单\n",
      "500米区域内咖啡馆和餐厅的平均账单\n",
      "500米区域内的咖啡馆和餐厅账单不适用\n",
      "咖啡馆和餐厅账单，500米区域平均低于500\n",
      "咖啡馆和餐厅账单，500米区域平均500-1000\n",
      "咖啡馆和餐厅账单，500米区域平均1000-1500\n",
      "咖啡馆和餐厅账单，500米区域平均1500-2500\n",
      "咖啡馆和餐厅账单，500米区域平均2500-4000\n",
      "咖啡馆和餐厅账单，500米区域平均超过4000\n",
      "500米区域内的大型教堂数量\n",
      "500米区域内的教堂数量\n",
      "500米区域内的清真寺数量\n",
      "500米区域内的休闲设施数量\n",
      "500米区域内的体育设施数量\n",
      "500米区域内的市场数量\n",
      "1000米区域中绿色区域的份额\n",
      "工业区在1000米区域中的份额\n",
      "1000米区域内的办公空间数量\n",
      "1000米区域的办公面积\n",
      "1000米区域内的购物中心数量\n",
      "1000米区域内的购物中心广场\n",
      "1000米区域内的咖啡馆或餐馆数量\n",
      "咖啡馆和餐厅1000米区域的最低平均账单\n",
      "1000米区域内咖啡馆和餐厅的最高平均账单\n",
      "1000米区域内咖啡馆和餐厅的平均账单\n",
      "1000米区域内的咖啡馆和餐厅账单不适用\n",
      "咖啡馆和餐馆账单，1000米区域平均低于500\n",
      "咖啡馆和餐厅账单，1000米区域平均500-1000\n",
      "咖啡馆和餐厅账单，1000米区域平均1000-1500\n",
      "咖啡馆和餐厅账单，1000米区域平均1500-2500\n",
      "咖啡馆和餐厅账单，1000米区域平均2500-4000\n",
      "咖啡馆和餐厅账单，1000米区域平均超过4000\n",
      "1000米区域内的大型教堂数量\n",
      "1000米区域内的教堂数量\n",
      "1000米区域内的清真寺数量\n",
      "1000米区域内的休闲设施数量\n",
      "1000米区域内的体育设施数量\n",
      "1000米区域内的市场数量\n",
      "1500米区域中的绿化区域份额\n",
      "工业区在1500米区域中的份额\n",
      "1500米区域内的办公空间数量\n",
      "1500米区域的办公面积\n",
      "1500米区域内的购物中心数量\n",
      "1500米区域内的购物中心广场\n",
      "1500米区域内的咖啡馆或餐馆数量\n",
      "咖啡馆和餐厅1500米区域的最低平均账单\n",
      "1500米区域内咖啡馆和餐厅的最高平均账单\n",
      "1500米区域内咖啡馆和餐厅的平均账单\n",
      "1500米区域内的咖啡馆和餐厅账单不适用\n",
      "咖啡馆和餐厅账单，1500米区域平均低于500\n",
      "咖啡馆和餐厅账单，1500米区域平均500-1000\n",
      "咖啡馆和餐厅账单，1500米区域平均1000-1500\n",
      "咖啡馆和餐厅账单，1500米区域平均1500-2500\n",
      "咖啡馆和餐厅账单，1500米区域平均2500-4000\n",
      "咖啡馆和餐厅账单，1500米区域平均超过4000\n",
      "1500米区域内的大型教堂数量\n",
      "1500米区域内的教堂数量\n",
      "1500米区域内的清真寺数量\n",
      "1500米区域内的休闲设施数量\n",
      "1500米区域内的体育设施数量\n",
      "1500米区域内的市场数量\n",
      "2000米区域中的绿化区域份额\n",
      "工业区在2000米区域中的份额\n",
      "2000米区域的办公空间数量\n",
      "2000米区域的办公面积\n",
      "2000米区域内的购物中心数量\n",
      "商场广场2000米区域\n",
      "1500米区域内的咖啡馆或餐馆数量\n",
      "咖啡馆和餐厅2000米区域的最低平均账单\n",
      "2000米区域内咖啡馆和餐厅的最高平均账单\n",
      "2000米区域内咖啡馆和餐厅的平均账单\n",
      "2000米区域内的咖啡馆和餐厅账单不适用\n",
      "咖啡馆和餐馆账单，2000米区域平均低于500\n",
      "咖啡馆和餐馆账单，2000米区域平均500-1000\n",
      "咖啡馆和餐厅账单，2000米区域平均1000-1500\n",
      "咖啡馆和餐厅账单，2000米区域平均1500-2500\n",
      "咖啡馆和餐厅账单，2000米区域平均2500-4000\n",
      "咖啡馆和餐厅账单，2000米区域平均超过4000\n",
      "2000米区域内的大型教堂数量\n",
      "2000米区域内的教堂数量\n",
      "2000米区域内的清真寺数量\n",
      "2000米区域内的休闲设施数量\n",
      "2000米区域内的体育设施数量\n",
      "2000米区域内的市场数量\n",
      "3000米区域中绿色区域的份额\n",
      "工业区在3000米区域中的份额\n",
      "3000米区域的办公空间数量\n",
      "3000米区域的办公面积\n",
      "3000米区域内的购物中心数量\n",
      "3000米区域内的购物广场\n",
      "1500米区域内的咖啡馆或餐馆数量\n",
      "3000米区域内咖啡馆和餐厅的最低平均账单\n",
      "3000米区域内咖啡馆和餐厅的最高平均账单\n",
      "3000米区域内咖啡馆和餐厅的平均账单\n",
      "3000米区域内的咖啡馆和餐厅账单不适用\n",
      "咖啡馆和餐馆账单，3000米区域平均低于500\n",
      "咖啡馆和餐厅账单，3000米区域平均500-1000\n",
      "咖啡馆和餐厅账单，3000米区域平均1000-1500\n",
      "咖啡馆和餐厅账单，3000米区域平均1500-2500\n",
      "咖啡馆和餐厅账单，3000米区域平均2500-4000\n",
      "咖啡馆和餐厅账单，3000米区域平均超过4000\n",
      "3000米区域内的大型教堂数量\n",
      "3000米区域内的教堂数量\n",
      "3000米区域内的清真寺数量\n",
      "3000米区域内的休闲设施数量\n",
      "3000米区域内的体育设施数量\n",
      "3000米区域内的市场数量\n",
      "5000米区域中的绿化区域份额\n",
      "工业区在5000米区域中的份额\n",
      "5000米区域的办公空间数量\n",
      "5000米区域的办公面积\n",
      "5000米区域内的购物中心数量\n",
      "5000米区域内的购物中心广场\n",
      "1500米区域内的咖啡馆或餐馆数量\n",
      "咖啡馆和餐厅5000米区域的最低平均账单\n",
      "5000米区域内咖啡馆和餐厅的最高平均账单\n",
      "5000米区域内咖啡馆和餐厅的平均账单\n",
      "5000米区域内的咖啡馆和餐厅账单不适用\n",
      "咖啡馆和餐厅账单，5000米区域平均低于500\n",
      "咖啡馆和餐厅账单，5000米区域平均500-1000\n",
      "咖啡馆和餐厅账单，5000米区域平均1000-1500\n",
      "咖啡馆和餐厅账单，5000米区域平均1500-2500\n",
      "咖啡馆和餐厅账单，5000米区域平均2500-4000\n",
      "咖啡馆和餐厅账单，5000米区域平均超过4000\n",
      "5000米区域内的大型教堂数量\n",
      "5000米区域内的教堂数量\n",
      "5000米区域内的清真寺数量\n",
      "5000米区域内的休闲设施数量\n",
      "5000米区域内的体育设施数量\n",
      "5000米区域内的市场数量\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>trans</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>price_doc</td>\n",
       "      <td>sale price (this is the target variable)</td>\n",
       "      <td>销售价格（这是目标变量）</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>id</td>\n",
       "      <td>transaction id</td>\n",
       "      <td>事务id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>timestamp</td>\n",
       "      <td>date of transaction</td>\n",
       "      <td>交易日期</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>full_sq</td>\n",
       "      <td>total area in square meters, including loggias...</td>\n",
       "      <td>总面积（平方米），包括凉亭、阳台和其他非住宅区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>life_sq</td>\n",
       "      <td>living area in square meters, excluding loggia...</td>\n",
       "      <td>居住面积（平方米），不包括凉亭、阳台和其他非住宅区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>437</th>\n",
       "      <td>church_count_5000</td>\n",
       "      <td>The number of churchs in 5000 meters zone</td>\n",
       "      <td>5000米区域内的教堂数量</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>438</th>\n",
       "      <td>mosque_count_5000</td>\n",
       "      <td>The number of mosques in 5000 meters zone</td>\n",
       "      <td>5000米区域内的清真寺数量</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>leisure_count_5000</td>\n",
       "      <td>The number of leisure facilities in 5000 meter...</td>\n",
       "      <td>5000米区域内的休闲设施数量</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>440</th>\n",
       "      <td>sport_count_5000</td>\n",
       "      <td>The number of sport facilities in 5000 meters ...</td>\n",
       "      <td>5000米区域内的体育设施数量</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>441</th>\n",
       "      <td>market_count_5000</td>\n",
       "      <td>The number of markets in 5000 meters zone</td>\n",
       "      <td>5000米区域内的市场数量</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>427 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      0                                                  1  \\\n",
       "2             price_doc           sale price (this is the target variable)   \n",
       "3                    id                                     transaction id   \n",
       "4             timestamp                                date of transaction   \n",
       "5               full_sq  total area in square meters, including loggias...   \n",
       "6               life_sq  living area in square meters, excluding loggia...   \n",
       "..                  ...                                                ...   \n",
       "437   church_count_5000          The number of churchs in 5000 meters zone   \n",
       "438   mosque_count_5000          The number of mosques in 5000 meters zone   \n",
       "439  leisure_count_5000  The number of leisure facilities in 5000 meter...   \n",
       "440    sport_count_5000  The number of sport facilities in 5000 meters ...   \n",
       "441   market_count_5000          The number of markets in 5000 meters zone   \n",
       "\n",
       "                         trans  \n",
       "2                 销售价格（这是目标变量）  \n",
       "3                         事务id  \n",
       "4                         交易日期  \n",
       "5      总面积（平方米），包括凉亭、阳台和其他非住宅区  \n",
       "6    居住面积（平方米），不包括凉亭、阳台和其他非住宅区  \n",
       "..                         ...  \n",
       "437              5000米区域内的教堂数量  \n",
       "438             5000米区域内的清真寺数量  \n",
       "439            5000米区域内的休闲设施数量  \n",
       "440            5000米区域内的体育设施数量  \n",
       "441              5000米区域内的市场数量  \n",
       "\n",
       "[427 rows x 3 columns]"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zhongwen = []\n",
    "import time\n",
    "#等待一秒\n",
    "\n",
    "for i in df[1]:\n",
    "    sen = i\n",
    "    jiexijson = json.loads(trans(sen))  # 翻译后的一个json文件\n",
    "    # print(jiexijson)\n",
    "    reslut = jiexijson['trans_result'][0]['dst']  # 从上面的json中提取翻译的结果\n",
    "    zhongwen.append(reslut)\n",
    "    time.sleep(1)\n",
    "    print(reslut)\n",
    "        \n",
    "df['trans'] = zhongwen\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "02813dd8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_origin = pd.read_csv('output.txt', delimiter=':', engine='python', header=None)\n",
    "df_origin.join(df.set_index(0), rsuffix='aa',on = 0, how = 'left')\\\n",
    "                                .drop('1aa',axis = 1)\\\n",
    "                                .to_excel('fanyi.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aba48fc8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
